package model;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

import javax.swing.JOptionPane;

public class ProductDAO {

	private List<Product> elementsList;
	private Connection con;

	public ProductDAO() {
		/*
		 * 2013.03.11 - changing ArrayList to LinkedList since the latter is
		 * optimized for addition or subtraction of items
		 */
		elementsList = new LinkedList<Product>();
	}

	public ProductDAO(LinkedList<Product> elementsList) {
		super();
		this.elementsList = elementsList;
	}

	public void addElement(Product element) {
		elementsList.add(element);
		/*
		 * System.out.println(
		 * "addPerson succeeded!\n*************Person added is:\n" +
		 * "idPerson: " + p.getId() + ", name: " + p.getName() + ", surname: " +
		 * p.getSurname() + ", Collateral client: " + p.getCc() + ", CC desc: "
		 * + p.getColClient() + ", isChkIC? = " + p.isChkIndirectClient());
		 */
	}

	public void removeElement(int index) {
		elementsList.remove(index);
	}

	public List<Product> getElementsList() {
		// Collections.unmodifiableList prevents other classes from modifying
		// the list by the reference retrieved
		return Collections.unmodifiableList(elementsList);
	}

	/*
	 * command to save table rows to a file
	 */
	public void saveToFile(File file) throws IOException {
		// fos is needed to open an output stream
		FileOutputStream fos = new FileOutputStream(file);
		// oos is the class that can write the stream
		ObjectOutputStream oos = new ObjectOutputStream(fos);

		/*
		 * I create an array of the people list, which may seem redundant but
		 * the oos' method use arrays, not lists.
		 */
		Product[] elementsArray = elementsList.toArray(new Product[elementsList.size()]);

		oos.writeObject(elementsArray);

		// I finally close the stream
		oos.close();
	}

	public void loadFromFile(File file) throws IOException,
			ClassNotFoundException {
		FileInputStream fis = new FileInputStream(file);
		ObjectInputStream ois = new ObjectInputStream(fis);

		// I get the info from the db to the peopleArray
		Product[] elementsArray = (Product[]) ois.readObject();

		// I first clear the ArrayList
		elementsList.clear();
		// Then, I set it with the new value of elementArray and transform it to
		// a List object
		elementsList.addAll(Arrays.asList(elementsArray));

		// I finally close the stream
		ois.close();
	}

	public void h2Connect() throws SQLException {
		// checks the mysql-connector
		try {
			Class.forName("org.h2.Driver");
		} catch (ClassNotFoundException e) {
			System.out
					.println("ClassNotFoundException while seeking the H2 driver: "
							+ e);
		}
		// create connection
		String url = "jdbc:h2:~/test", sqlUname = "sa", sqlPass = "";
		// sqlPass = "r00t";

		// if connection is already created, just return
		if (con != null)
			return;
		// otherwise, create it
		con = DriverManager.getConnection(url, sqlUname, sqlPass);
		System.out.println("Connected: " + con);
	}

	public void disconnect() throws SQLException {
		System.out.println("...closing now.");
		if (con != null) {
			con.close();
			System.out.println("isClosed? " + con.isClosed());
		}
	}

	/*
	 * Method to insert and or update a list of people
	 */
	public void saveToDB() throws SQLException {
		String csql, isql, usql, desc;
		int col = 0, colClient = 0;
		boolean isChkIndirectClient;
		/*
		 * id will be used to check the id's while I go through the elements
		 * reps to compare occurrences
		 */
		int id = -1, reps = -1;
		/*
		 * sql queries with question marks are used in prepared statements since
		 * they are more secure due to being more difficult to be attacked by a
		 * code injection tactic.
		 */
		csql = "select count(*) as reps from People where id = ?";
		isql = "insert into People (id, name, surname, collateral_client, is_direct_client) values(?, ?, ?, ?, ?)";
		usql = "update People set name = ?, surname = ?, collateral_client = ?, is_direct_client = ? where id = ?";
		PreparedStatement cps = null, eps = null;
		ResultSet rs = null;

		cps = con.prepareStatement(csql);

		for (Product element : elementsList) {
			id = element.getId();
			desc = element.getDesc();
			
			System.out.println("hardcoded values created for insertion - id: "
					+ id + ", desc: " + desc);
			// first parameter is the index of the wildcard, this once there's
			// just one though
			cps.setInt(1, id);
			// execute the query
			rs = cps.executeQuery();
			// go to cursor in the first position
			rs.next();
			// get the count
			reps = rs.getInt("reps");

			if (reps == 0) {
				// preparing the insert with the execution prepared statement
				eps = con.prepareStatement(isql);
				// col will iterate setting values through the column numbers
				// dynamically
				col = 1;
				eps.setInt(col++, id);
				eps.setString(col++, desc);
				
				System.out.println("Inserting person with id: " + id + " ("
						+ desc + ").");
				eps.executeUpdate();
			} else {
				// preparing the update with the execution prepared statement
				eps = con.prepareStatement(usql);
				// col will iterate setting values through the column numbers
				// dynamically
				col = 1;
				eps.setString(col++, desc);
				eps.setInt(col++, id);

				System.out.println("Updating person with id: " + id + " ("
						+ desc + ").");
				eps.executeUpdate();
			}
		}
		csql = "cleaned from saveToDB()";
		isql = "cleaned from saveToDB()";
		usql = "cleaned from saveToDB()";
		cps.close();
		eps.close();
	}

	public void loadFromDB() throws SQLException {
		String csql, isql, usql, desc;
		int col = 0;
		/*
		 * very very first thing, I'll declare my prepared statement
		 */
		csql = "select id, name, surname, collateral_client, is_direct_client from people order by name";
		Statement select = con.createStatement();
		// first thing, I'll clear the list
		elementsList.clear();

		// I execute the query and load it in the rs resultset
		ResultSet rs = select.executeQuery(csql);

		while (rs.next()) {
			/*
			 * System.out.println("id: " + rs.getInt("id") + ", name: " +
			 * rs.getString("name") + ", surname: " + rs.getString("surname"));
			 */

			/*
			 * people.add(new Person(rs.getInt("id"), rs.getString("name"), rs
			 * .getString("surname"), CollateralClient.valueOf(rs
			 * .getString("collateral_client")), rs
			 * .getBoolean("isDirectClient")));
			 */
			Product tmp = new Product(rs.getInt("id"), rs.getString("desc"));
			System.out.println("getting element: " + tmp);
			elementsList.add(tmp);
		}

		/*
		 * finally, I close the resultset and the statement
		 */
		csql = "cleaned from loadFromDB()";
		select.close();
		rs.close();
	}

}
